-- 首中心未建包重量段分析
WITH centerarrival_base AS
(
    select
        waybill_no
        ,scan_time
        ,scan_site_code
        ,scan_site
        ,to_date(scan_time) AS scan_date
    from(
        SELECT
            waybill_no
            ,scan_time
            ,scan_site_code
            ,scan_site
            ,package_code
            ,row_number() over(partition by waybill_no order by scan_time) as rn
        FROM jms_dwd.dwd_tab_barscan_centerarrival_base_dt t1
        JOIN
        (
            SELECT
                code
                ,name
            FROM jms_dim.dim_network_whole_massage
            WHERE is_delete = 1
            AND is_enable = 1
            AND virt_name = '京津冀代理区'
            AND network_type = 4
        ) t2
        ON t1.scan_site_code = t2.code
        WHERE dt >= date_add('{{ execution_date | cst_ds }}', -21)
        AND dt <= '{{ execution_date | cst_ds }}'
    ) a
    where
        rn =1
	AND to_date(scan_time) >= date_sub('{{ execution_date | cst_ds }}', 6)
	AND to_date(scan_time) <= '{{ execution_date | cst_ds }}'
	AND package_code is null
), bagging_detail AS
(
	SELECT  waybill_no
	       ,package_code
	       ,scan_site_code
	       ,scan_site
	       ,scan_time
	       ,scan_type
	FROM jms_dwd.dwd_tab_barscan_bagging_base_dt
	WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 15)
	AND dt <= '{{ execution_date | cst_ds }}'
	AND waybill_no like 'JT%' 
)
, first_center_detail as(
    SELECT  waybill_no
           ,first_center_network_code
    FROM jms_dwd.dwd_wide_unsign_summary_waybill_dt
    WHERE dt >= date_sub('{{ execution_date | cst_ds }}',  15)
    AND dt <= '{{ execution_date | cst_ds }}' )
, yl_oms_oms_waybill_incre_unsame_city as(
    SELECT  waybill_no
           ,pick_network_code
           ,pick_network_name
           ,t2.agent_code
           ,t2.agent_name
           ,package_inside_charge_weight
    FROM jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t1
    JOIN
    (
        SELECT  code
               ,name
               ,agent_code
               ,agent_name
               ,zone_code
        FROM jms_dim.dim_network_whole_massage
        WHERE is_delete = 1
        AND is_enable = 1
        AND virt_name = '京津冀代理区'
        AND network_type = 6
    ) t2
    ON t1.pick_network_code = t2.code
    left join
        jms_dim.dim_network_whole_massage t3
            on t1.dispatch_network_code = t3.code
    WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 30)
    AND dt <= '{{ execution_date | cst_ds }}'
    AND package_inside_charge_weight > 0
    AND package_inside_charge_weight <= 3.2
    and nvl(t2.zone_code, '0') <> nvl(t3.zone_code, '1')
)
, yl_oms_oms_waybill_incre_same_city as(
    SELECT  waybill_no
           ,pick_network_code
           ,pick_network_name
           ,t2.agent_code
           ,t2.agent_name
           ,package_inside_charge_weight
    FROM jms_dwd.dwd_yl_oms_oms_waybill_incre_dt t1
    JOIN
    (
        SELECT  code
               ,name
               ,agent_code
               ,agent_name
               ,zone_code
        FROM jms_dim.dim_network_whole_massage
        WHERE is_delete = 1
        AND is_enable = 1
        AND virt_name = '京津冀代理区'
        AND network_type = 6
    ) t2
    ON t1.pick_network_code = t2.code
    left join
        jms_dim.dim_network_whole_massage t3
            on t1.dispatch_network_code = t3.code
    WHERE dt >= date_sub('{{ execution_date | cst_ds }}', 30)
    AND dt <= '{{ execution_date | cst_ds }}'
    AND package_inside_charge_weight > 0
    AND package_inside_charge_weight <= 3.2
    and nvl(t2.zone_code, '0') = nvl(t3.zone_code, '1')
)
insert overwrite table jms_dm.dm_waybill_unbagging_weight_sum_dt partition (dt)
SELECT  a.scan_site_code
       ,scan_site
       ,agent_code
       ,agent_name
       ,pick_network_code
       ,pick_network_name
       ,scan_date
       ,SUM(if(package_inside_charge_weight <= 1.0 ,1,0))                                        AS weight_range1_cnt
       ,SUM(if(package_inside_charge_weight > 1.0 AND package_inside_charge_weight <= 2.0 ,1,0)) AS weight_range2_cnt
       ,SUM(if(package_inside_charge_weight > 2.0 AND package_inside_charge_weight <= 3.0 ,1,0)) AS weight_range3_cnt
       ,SUM(if(package_inside_charge_weight > 3.0 ,1,0))                                         AS weight_range4_cnt
       ,scan_date
FROM(
    SELECT
        tmp.waybill_no
        ,tmp.scan_site_code
        ,tmp.scan_site
        ,tmp.agent_code
        ,tmp.agent_name
        ,tmp.pick_network_code
        ,tmp.pick_network_name
        ,tmp.scan_date
        ,tmp.package_inside_charge_weight
        ,row_number() over (partition by tmp.waybill_no ORDER BY tmp.scan_time ) AS rn
    FROM(
        -- 剔除同城件
        SELECT
            t3.waybill_no
            ,t3.scan_time
            ,t3.scan_site_code
            ,concat(t3.scan_site, '|非同城件') as scan_site
            ,t3.scan_date
            ,t4.pick_network_code
            ,pick_network_name
            ,t4.agent_code
            ,t4.agent_name
            ,package_inside_charge_weight
            ,row_number() over (partition by t3.waybill_no ORDER BY t3.scan_time ) AS rn
        FROM centerarrival_base t3
        JOIN yl_oms_oms_waybill_incre_unsame_city t4
        ON t3.waybill_no = t4.waybill_no
        LEFT JOIN bagging_detail t5
        ON t3.waybill_no = t5.waybill_no AND t3.scan_site_code = t5.scan_site_code
        JOIN first_center_detail t6
        ON t3.waybill_no = t6.waybill_no AND t3.scan_site_code = t6.first_center_network_code
        WHERE t5.waybill_no is null

        union all

        -- 同城件
        SELECT
            t3.waybill_no
            ,t3.scan_time
            ,t3.scan_site_code
            ,concat(t3.scan_site, '|同城件') as scan_site
            ,t3.scan_date
            ,t4.pick_network_code
            ,pick_network_name
            ,t4.agent_code
            ,t4.agent_name
            ,package_inside_charge_weight
            ,row_number() over (partition by t3.waybill_no ORDER BY t3.scan_time ) AS rn
        FROM centerarrival_base t3
        JOIN yl_oms_oms_waybill_incre_same_city t4
        ON t3.waybill_no = t4.waybill_no
        LEFT JOIN bagging_detail t5
        ON t3.waybill_no = t5.waybill_no AND t3.scan_site_code = t5.scan_site_code
        JOIN first_center_detail t6
        ON t3.waybill_no = t6.waybill_no AND t3.scan_site_code = t6.first_center_network_code
        WHERE t5.waybill_no is null
    )tmp
        left join bagging_detail t7
                ON tmp.waybill_no = t7.waybill_no
        WHERE tmp.rn = 1
        and
        not (
            tmp.scan_site_code = '701002'               -- 北京转运中心
            and nvl(t7.scan_site_code,'0')  = '731601'  -- 廊坊临空转运中心
        )
) a
WHERE rn = 1
GROUP BY  a.scan_site_code
         ,scan_site
         ,agent_code
         ,agent_name
         ,pick_network_code
         ,pick_network_name
         ,scan_date
distribute by 1
;
